package com.mkvld.appietijdenv2.databases;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.mkvld.appietijdenv2.data.PeriodShifts;
import com.mkvld.appietijdenv2.data.Preferences;
import com.mkvld.appietijdenv2.data.Shift;
import com.mkvld.appietijdenv2.data.Utils;
import com.mkvld.appietijdenv2.data.WeekShifts;
import com.mkvld.appietijdenv2.data.Shift.ShiftType;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ShiftDatabase extends SQLiteOpenHelper {
	private static final String TAG = "ShiftDatabase";
	private static final String DB_NAME = "ShiftDatabase";
	private static final int DB_VERSION = 5;
	
	private static final String TABLE_SHIFTS = "ShiftTable";
	private static final String KEY_START = "start";
	private static final String KEY_END = "end";
	private static final String KEY_DAY = "day";
	private static final String KEY_GOOGLE = "google";
	private static final String KEY_TYPE = "type";
	private static final String[] KEYS = {
		KEY_START, KEY_END, KEY_DAY, KEY_GOOGLE, KEY_TYPE
	}; 
	public static final int INDEX_KEY_START = 0;
	public static final int INDEX_KEY_END = 1;
	public static final int INDEX_KEY_DAY = 2;
	public static final int INDEX_KEY_GOOGLE = 3;
	public static final int INDEX_KEY_TYPE = 4;
	
	private static final String CREATE_SHIFTS = 
			"CREATE TABLE " + TABLE_SHIFTS + " ("
					+ KEY_START + " TEXT, " + KEY_END + " TEXT,"
					+ KEY_DAY + " INTEGER," + KEY_GOOGLE + " INTEGER,"
					+ KEY_TYPE + " TEXT)";
	
	private static long WEEK_MILLIS = 604800000;
	
	private Context context;
	public static ShiftDatabase db;
	
	
	public ShiftDatabase(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_SHIFTS);		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SHIFTS);
		
		onCreate(db);
	}
	
	private Shift cursorToShift(Cursor cursor) {
		double start = Double.parseDouble(cursor.getString(INDEX_KEY_START));
		double end = Double.parseDouble(cursor.getString(INDEX_KEY_END));
		long dayMillis = Long.parseLong(cursor.getString(INDEX_KEY_DAY));
		int[] ywd = millisToYWD(dayMillis);
		int googleId = Integer.parseInt(cursor.getString(INDEX_KEY_GOOGLE));
		String typeString = cursor.getString(INDEX_KEY_TYPE);
		ShiftType type = ShiftType.PRODUCTIVE;
		if (typeString != null)
			type = ShiftType.valueOf(typeString);
		return new Shift(type, ywd[0], ywd[1], ywd[2], start, end, googleId);
	}
	
	public void addShift(Shift shift) {
		if (canAdd(shift)) {
			ContentValues cv = new ContentValues();
			cv.put(KEY_START, shift.getStart());
			cv.put(KEY_END, shift.getEnd());
			
			cv.put(KEY_DAY, dayToMillis(shift.getYear(), shift.getWeek(), shift.getDay()));
			cv.put(KEY_GOOGLE, shift.getGoogleId());
			cv.put(KEY_TYPE, shift.getType().name());
			
			SQLiteDatabase db = this.getWritableDatabase();
			db.insert(TABLE_SHIFTS, null, cv);
			db.close();
		}
	}
	
	
	
	private boolean canAdd(Shift shift) {
		SQLiteDatabase db = this.getReadableDatabase();
		String selection = KEY_DAY + " = ?";
		String[] selectionArgs = {
				Long.toString(dayToMillis(shift.getYear(), shift.getWeek(), shift.getDay()))
		};
		Cursor cursor = db.query(TABLE_SHIFTS, KEYS, selection, selectionArgs, null, null, null);
		if (cursor.moveToFirst()) {
			if (cursorToShift(cursor).conflict(shift))
				return false;
		}
		return true;
	}
	
	private boolean canEdit(Shift prevShift, Shift newShift) {
		SQLiteDatabase db = this.getReadableDatabase();
		
		String selection = KEY_DAY + " = ?";
		String[] selectionArgs = {
				Long.toString(dayToMillis(newShift.getYear(), newShift.getWeek(), newShift.getDay()))
		};
		Cursor cursor = db.query(TABLE_SHIFTS, KEYS, selection, selectionArgs, null, null, null);
		if (cursor.moveToFirst()) {
			Shift shift = cursorToShift(cursor);
			if (!shift.equals(prevShift) && shift.conflict(newShift))
				return false;
		}
		return true;
	}
	
	public boolean editShift(Shift prevShift, Shift newShift) {
		if (canEdit(prevShift, newShift)) {
			deleteShift(prevShift);
			addShift(newShift);
			return true;
		}
		return false;
	}

	public void deleteShift(Shift shift) {
		deleteShift(shift.getYear(), shift.getWeek(), shift.getDay(), shift.getStart());
	}
	
	public void deleteShift(int year, int week, int day, double start) {
		SQLiteDatabase db = this.getWritableDatabase();
		String selection = KEY_DAY + " = ? AND "
				+ KEY_START + " = ?";
		String[] selectionArgs = {
				Long.toString(dayToMillis(year, week, day)),
				Double.toString(start)
		};
		db.delete(TABLE_SHIFTS, selection, selectionArgs);
		db.close();
	}
	
	public WeekShifts getWeekShifts(int year, int week) {
		return new WeekShifts(Preferences.getContract(context), getShifts(getWeekStart(year, week), WEEK_MILLIS));
	}
	
	public PeriodShifts getPeriodShifts(int year, int period) {
		return new PeriodShifts(Preferences.getContract(context), getShifts(getPeriodStart(year, period), WEEK_MILLIS * 4));
	}
	
	private List<Shift> getShifts(long from, long duration) {
		List<Shift> shifts = new ArrayList<Shift>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			String selection = KEY_DAY + " >= ? AND " + KEY_DAY + " < ?";
			String[] selectionArgs = {Long.toString(from), Long.toString(from + duration)};
			Cursor cursor = db.query(
					TABLE_SHIFTS, KEYS, selection, selectionArgs, null, null, null); 
			if (cursor.moveToFirst()) {
				do {
					shifts.add(cursorToShift(cursor));
				} while (cursor.moveToNext());
			}
			cursor.close();
		} catch (Exception e) {
			Log.e(TAG, "getWeekShifts failed with reason: "+e.getLocalizedMessage());
		}
		return shifts;
	}
	
	

	public List<Shift> getShifts() {
		return getShifts(0, 2524629600000l);
	}
	
	private static long dayToMillis(int year, int week, int day) {
		Calendar cal = Calendar.getInstance();
		cal.setFirstDayOfWeek(Calendar.MONDAY);
		cal.set(Calendar.YEAR, year);
		cal.set(Calendar.DAY_OF_WEEK, (day + 1) % 7 + 1);
		cal.set(Calendar.WEEK_OF_YEAR, week + 1);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		return cal.getTimeInMillis();
	}
	
	private static int[] millisToYWD(long millis) {
		int[] ywd = new int[3];
		Calendar cal = Calendar.getInstance();
		cal.setTimeInMillis(millis);
		ywd[0] = cal.get(Calendar.YEAR);
		ywd[1] = cal.get(Calendar.WEEK_OF_YEAR) - 1;
		ywd[2] = (cal.get(Calendar.DAY_OF_WEEK) + 5) % 7;
		return ywd;
	}
	
	private static long getWeekStart(int year, int week) {
		Calendar cal = Calendar.getInstance(Utils.locale);
		cal.set(Calendar.YEAR, year);
		cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
		cal.set(Calendar.WEEK_OF_YEAR, week + 1);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		return cal.getTimeInMillis();
	}
	
	public static long getPeriodStart(int year, int period) {
		return getWeekStart(year, period * 4);
	}
	
	public double averageInLast12Weeks(int year, int week, int day) {
		SQLiteDatabase db = this.getReadableDatabase();
		int daysWorked = 0;
		double totalHours = 0;
		int currWeek = week;
		String selection = KEY_DAY + " = ?";
		while (--currWeek >= week - 12) {
			String[] selectionArgs = {
					Long.toString(dayToMillis(year, currWeek, day))
			};
			Cursor cursor = db.query(TABLE_SHIFTS, KEYS, selection, selectionArgs, null, null, KEY_DAY);
			if (cursor.moveToFirst()) {
				daysWorked++;
				do {
					totalHours += Double.parseDouble(cursor.getString(INDEX_KEY_END)) - Double.parseDouble(cursor.getString(INDEX_KEY_START));
				} while (cursor.moveToNext());
			}
			cursor.close();
		}
		
		
		if (daysWorked < 4)
			return 0;
		else 
			return totalHours / daysWorked;
	}

	public void clear() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_SHIFTS, null, null);
		db.close();
	}

	public void clearWeek(int year, int week) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_SHIFTS, KEY_DAY + " >= ? AND " + KEY_DAY + " < ?", new String[] {
			Long.toString(getWeekStart(year, week)),
			Long.toString(getWeekStart(year, week + 1))
		});
	}

	
	
}
